<?php
/** Tiehui.Lu 512701323@qq.com utf-8 上午10:26:11 2009-7-3 */
function fetchArticleByTop($category_id = NULL, $latest = NULL, $user_id = NULL) {
	global $db;
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', '*' );
	$select->join ( DB_PRE . 'article_category AS b', 'a.id = b.article_id', array ('article_id', 'category_id' ) );
	$select->join ( DB_PRE . 'category AS c', 'c.id = b.category_id', array ('name', 'father_id', 'symbol', 'template_id', 'template_id_article' ) );
	if ($category_id != NULL) {
		$select->where ( 'b.category_id IN (?)', $category_id );
	}
	if ($user_id != NULL) {
		$select->where ( 'a.user_id = ?', $user_id );
	}
	$select->where ( 'a.state  = ?', 1 );
	$select->order ( 'a.sort DESC' );
	$select->order ( 'a.id DESC' );
	if ($latest != NULL) {
		$select->limit ( $latest, 0 );
	}
	//echo $select->__toString ();
	return $db->fetchAll ( $select );
}

function fetchArticleByTime($category_id = 0, $from = null, $to = null, $latest = NULL, $user_id = NULL) {
	global $db;
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', '*' );
	$select->join ( DB_PRE . 'article_category AS b', 'a.id = b.article_id', array ('article_id', 'category_id' ) );
	$select->join ( DB_PRE . 'category AS c', 'c.id = b.category_id', array ('name', 'father_id', 'symbol', 'template_id', 'template_id_article' ) );
	if ($category_id != NULL) {
		$select->where ( 'b.category_id IN (?)', $category_id );
	}
	if ($user_id != NULL) {
		$select->where ( 'a.user_id = ?', $user_id );
	}
	$select->where ( 'a.state  = ?', 1 );
	if ($from != NULL || $from != '') {
		$d = split ( "-", $from );
		$time = mktime ( 0, 0, 0, $d [1], $d [2], $d [0] );
		$select->where ( 'a.time >= ?', $time );
	}
	if ($to != NULL || $to != '') {
		$d = split ( "-", $to );
		$time = mktime ( 0, 0, 0, $d [1], $d [2], $d [0] );
		$select->where ( 'a.time < ?', $time );
	}
	$select->order ( 'a.sort DESC' );
	$select->order ( 'a.id DESC' );
	if ($latest != NULL) {
		$select->limit ( $latest, 0 );
	}
	//echo $select->__toString ();
	return $db->fetchAll ( $select );
}

function fetchArticleById($category_id = 0, $from = null, $to = null, $latest = NULL, $user_id = NULL) {
	global $db;
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', '*' );
	$select->join ( DB_PRE . 'article_category AS b', 'a.id = b.article_id', array ('article_id', 'category_id' ) );
	$select->join ( DB_PRE . 'category AS c', 'c.id = b.category_id', array ('name', 'father_id', 'symbol', 'template_id', 'template_id_article' ) );
	if ($category_id != NULL) {
		$select->where ( 'b.category_id IN (?)', $category_id );
	}
	if ($user_id != NULL) {
		$select->where ( 'a.user_id = ?', $user_id );
	}
	$select->where ( 'a.state  = ?', 1 );
	if ($from != NULL || $from != '') {
		$select->where ( 'a.id >= ?', $from );
	}
	if ($to != NULL || $to != '') {
		$select->where ( 'a.id < ?', $to );
	}
	$select->order ( 'a.sort DESC' );
	$select->order ( 'a.id DESC' );
	if ($latest != NULL) {
		$select->limit ( $latest, 0 );
	}
	//echo $select->__toString ();
	return $db->fetchAll ( $select );
}

function fetchArticle($category_id, $page = 1, $offset = 20, $user_id = null, $state = 1) {
	global $db;
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', '*' );
	$select->join ( DB_PRE . 'article_category AS b', 'a.id = b.article_id', array ('article_id', 'category_id', 'template_id' ) );
	$select->join ( DB_PRE . 'user AS c', 'a.user_id = c.id', 'name' );
	$select->where ( 'b.category_id = ?', $category_id );
	$select->where ( 'a.state  IN (?)', $state );
	if ($user_id != null) {
		$select->where ( 'c.id  = (?)', $user_id );
	}
	$select->order ( 'a.sort DESC' );
	$select->order ( 'a.id DESC' );
	$select->limit ( $offset, ($page - 1) * $offset );
	//echo $select->__toString ();
	return $db->fetchAll ( $select );
}

function findCount($article_id, $user_id = null, $state = 1) {
	global $db;
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', 'count(*) count' );
	$select->join ( DB_PRE . 'article_category AS b', 'a.id = b.article_id', '' );
	if ($user_id) {
		//$select->join ( DB_PRE . 'user AS c', 'a.user_id = c.id', 'name' );
		$select->join ( DB_PRE . 'user AS c', 'a.user_id = c.id', '' );
		$select->where ( 'c.id  IN (?)', $user_id );
	}
	$select->where ( 'b.category_id = ?', $article_id );
	$select->where ( 'a.state  = ?', $state );
	//echo $select->__toString ();
	$d = $db->fetchAll ( $select );
	return $d [0] ['count'];
}

function findCategoryByAid($article_id) {
	global $db;
	$select = $db->select ();
	$select->from ( DB_PRE . 'category AS a', '*' );
	$select->join ( DB_PRE . 'article_category AS b', 'a.id = b.category_id', array ('article_id', 'category_id', 'template_id' ) );
	$select->where ( 'b.article_id = ?', $article_id );
	return $db->fetchRow ( $select );
}

//Stat.  
function ViewStatArticleCategoryByCid($category_id, $user_id = null, $type = 'article') {
	global $db;
	$select = $db->select ();
	$select->from ( DB_PRE . 'article_category AS a', '*' );
	$select->join ( DB_PRE . 'stat AS b', 'a.article_id = b.foreign_id', '*' );
	if ($user_id) {
		$select->join ( DB_PRE . 'article AS c', 'a.article_id = c.id', 'user_id' );
		$select->where ( 'c.user_id = ?', $user_id );
	}
	$select->where ( 'a.category_id = ?', $category_id );
	$select->where ( 'b.type = ?', $type );
	//echo $select->__toString ()."<br />";
	return $db->fetchAll ( $select );
}

//$sort:day\week\month\total|''
function viewArticleCategoryByCid($category_id, $sort = '', $user_id = null) {
	global $db;
	
	if ($sort == 'day') {
		$start = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ), date ( "Y" ) );
		$end = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ) + 1, date ( "Y" ) );
	} elseif ($sort == 'week') {
		$start = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ) - date ( 'w' ), date ( "Y" ) );
		$end = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ) + 6 - date ( 'w' ), date ( "Y" ) );
	} elseif ($sort == 'month') {
		$start = mktime ( 0, 0, 0, date ( "m" ), 0, date ( "Y" ) );
		$end = mktime ( 0, 0, 0, date ( "m" ) + 1, 0, date ( "Y" ) );
	} else {
		$start = 0;
		$end = mktime ();
	}
	
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', array ('user_id', 'time' ) );
	$select->join ( DB_PRE . 'article_category AS b', 'a.id = b.article_id', '*' );
	$select->where ( 'b.category_id IN (?)', $category_id );
	$select->where ( 'a.state  = ?', 1 );
	if ($user_id) {
		$select->where ( 'a.user_id = ?', $user_id );
	}
	$select->where ( 'a.time >= ?', $start );
	$select->where ( 'a.time < ?', $end );
	//echo $select->__toString () . "<BR />";
	$rowset = $db->fetchAll ( $select );
	return count ( $rowset );
}

//Article hits TOP: $sort:day\week\month\total|''
function topArticle($sort = '', $limit = 10, $type = 'article') {
	global $db;
	
	if ($sort == 'day') {
		$start = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ), date ( "Y" ) );
		$end = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ) + 1, date ( "Y" ) );
		$order = 'b.hits_day';
	} elseif ($sort == 'week') {
		$start = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ) - date ( 'w' ), date ( "Y" ) );
		$end = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ) + 6 - date ( 'w' ), date ( "Y" ) );
		$order = 'b.hits_week';
	} elseif ($sort == 'month') {
		$start = mktime ( 0, 0, 0, date ( "m" ), 0, date ( "Y" ) );
		$end = mktime ( 0, 0, 0, date ( "m" ) + 1, 0, date ( "Y" ) );
		$order = 'b.hits_month';
	} else {
		$start = 0;
		$end = mktime ();
		$order = 'b.hits';
	}
	
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', array ('id', 'user_id', 'title', 'url' ) );
	$select->join ( DB_PRE . 'stat AS b', 'a.id = b.foreign_id', '*' );
	$select->where ( 'a.state  = ?', 1 );
	$select->where ( 'b.type = ?', $type );
	$select->where ( 'b.type = ?', $type );
	$select->where ( 'a.time >= ?', $start );
	$select->where ( 'a.time < ?', $end );
	$select->order ( $order . ' DESC' );
	$select->order ( 'a.id DESC' );
	$select->limit ( $limit, 0 );
	
	//echo $select->__toString ();
	return $db->fetchAll ( $select );
}

//Article num TOP: $sort:day\week\month\total|''
function topArticleNum($sort = '') {
	global $db;
	
	if ($sort == 'day') {
		$start = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ), date ( "Y" ) );
		$end = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ) + 1, date ( "Y" ) );
	} elseif ($sort == 'week') {
		$start = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ) - date ( 'w' ), date ( "Y" ) );
		$end = mktime ( 0, 0, 0, date ( "m" ), date ( "d" ) + 6 - date ( 'w' ), date ( "Y" ) );
	} elseif ($sort == 'month') {
		$start = mktime ( 0, 0, 0, date ( "m" ), 0, date ( "Y" ) );
		$end = mktime ( 0, 0, 0, date ( "m" ) + 1, 0, date ( "Y" ) );
	} else {
		$start = 0;
		$end = mktime ();
	}
	
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', array ('user_id', 'count(*) AS count' ) );
	$select->join ( DB_PRE . 'user AS b', 'a.user_id = b.id', array ('id', 'name' ) );
	$select->where ( 'a.state  = ?', 1 );
	$select->where ( 'a.time >= ?', $start );
	$select->where ( 'a.time < ?', $end );
	$select->group ( 'a.user_id' );
	$select->order ( 'count DESC' );
	
	//echo $select->__toString ();
	return $db->fetchAll ( $select );
}

//USER Article hits TOP: $sort:day\week\month\total|''
function topUserArticleHits($sort = '', $type = "article") {
	global $db;
	
	if ($sort == 'day') {
		$order = 'hits_day';
	} elseif ($sort == 'week') {
		$order = 'hits_week';
	} elseif ($sort == 'month') {
		$order = 'hits_month';
	} else {
		$order = 'hits';
	}
	
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', array ('id', 'user_id' ) );
	$select->join ( DB_PRE . 'stat AS b', 'a.id = b.foreign_id', array ('SUM(hits) hits', 'SUM(hits_day) hits_day', 'SUM(hits_week) hits_week', 'SUM(hits_month) hits_month' ) );
	$select->join ( DB_PRE . 'user AS c', 'a.user_id = c.id', array ('id', 'name' ) );
	$select->where ( 'a.state  = ?', 1 );
	$select->where ( 'b.type  = ?', $type );
	$select->group ( 'a.user_id' );
	$select->order ( $order . ' DESC' );
	//echo $select->__toString ();
	return $db->fetchAll ( $select );
}

/**
 * Omnipotent function 
 *
 * @param Int (Array) $pd [channel id]
 * @param Int $tj recommend id]
 * @param Int $page
 * @param Int $offset
 * @param String $order [time,hits,hits_day,hits_week,hits_month]
 * @return Array()
 */
function JData($pd, $tj = null, $offset = 20, $order = 'time', $list = false) {
	global $db;
	$ret ['pd'] = $pd;
	$ret ['tj'] = $tj;
	$ret ['offset'] = $offset;
	$ret ['order'] = $order;
	$ret ['list'] = $list;
	$d = array ();
	$pdArr = array ();
	
	//channel id Array()
	$categoryTable = new CategoryTable ( );
	if (! is_array ( $pd )) {
		$d = $categoryTable->findMain ( $pd );
		// Symbol
		$symbol = $categoryTable->findSymbol ( $pd );
		$ret ['symbol'] = $symbol;
	} else {
		for($i = 0; $i < count ( $pd ); $i ++) {
			$d = array_merge ( $d, $categoryTable->findMain ( $pd [$i] ) );
		}
	}
	for($i = 0; $i < count ( $d ); $i ++) {
		$pdArr [] = $d [$i] ['id'];
	}
	
	//Count
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', array ('count(*) count' ) );
	$select->join ( DB_PRE . 'article_category AS b', 'a.id = b.article_id', '' );
	if ($tj != null || $tj != '' || $tj != 0) {
		$select->join ( DB_PRE . 'article_recommend AS c', 'a.id = c.article_id', '' );
	}
	$select->where ( 'a.state  = ?', 1 );
	$select->where ( 'b.category_id  IN (?)', $pdArr );
	if ($tj != null || $tj != '' || $tj != 0) {
		$select->where ( 'c.recommend_id  IN (?)', $tj );
	}
	$d = $db->fetchAll ( $select );
	$ret ['count'] = $d [0] ['count'];
	
	//Data
	$select = $db->select ();
	$select->from ( DB_PRE . 'article AS a', '*' );
	$select->join ( DB_PRE . 'article_category AS b', 'a.id = b.article_id', array ('category_id' ) );
	if ($tj != null || $tj != '' || $tj != 0) {
		$select->join ( DB_PRE . 'article_recommend AS c', 'a.id = c.article_id', '' );
	}
	if ($order != 'time') {
		$select->join ( DB_PRE . 'stat AS d', 'a.id = d.foreign_id', '*' );
	}
	$select->where ( 'a.state  = ?', 1 );
	$select->where ( 'b.category_id  IN (?)', $pdArr );
	if ($tj != null || $tj != '' || $tj != 0) {
		$select->where ( 'c.recommend_id  IN (?)', $tj );
	}
	$select->order ( 'a.sort DESC' );
	$select->order ( $order . ' DESC' );
	if (! $list) {
		$select->limit ( $offset, 0 );
	}
	//echo $select->__toString ();
	$ret ['data'] = $db->fetchAll ( $select );
	
	return $ret;
}

function fetchUserByCategoryId($id) {
	global $db;
	$select = $db->select ();
	$select->from ( DB_PRE . 'user_category AS a', '*' );
	$select->join ( DB_PRE . 'user AS b', 'a.user_id = b.id', '*' );
	$select->where ( 'a.category_id = ?', $id );
	return $db->fetchAll ( $select );
}

?>